package tgc.edu.fhh.service;



import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tgc.edu.fhh.custom.BaseService;
import tgc.edu.fhh.entity.Course;
import tgc.edu.fhh.repository.CourseRepository;
@Service
public class CourseService extends BaseService<Course, Integer>{
	@Autowired
	private CourseRepository coudao;
	@Resource
	private EntityManager entityManager;

	public List<Course> findByTbClassIdAndCourseId(Integer bjId, Integer couId) {
		return coudao.findByTbClassIdAndCourseId(bjId,couId);
	}

	public List<Course> findByTbClassId(Integer bjId) {
		return coudao.findByTbClassId(bjId);
	}

	public List<List<Object>> findByCourseAndTbClassId(List<Course> cou, Integer bjId) {
		String hql="select g.student.stuNo,g.student.name,";
		for(Course course : cou) {
			hql+="sum(case when g.course.id="+course.getId()+" then g.grade else 0 end),";
		}
		hql+="sum(g.grade) as totol";
		hql+=" from Grade g ";
		hql+=" where g.student.tbclass.id="+bjId;
		hql+=" group by g.student.id";
		hql+=" order by totol desc";
		Query query = entityManager.createQuery(hql);
		entityManager.close();
		return query.getResultList();
	}


}
